USE xml_test
GO
ALTER TABLE dbo.Regex_column 
DROP CONSTRAINT Regex_x_validator
GO
DROP TABLE dbo.Regex_column
GO
DROP FUNCTION dbo.x_validator
GO
-- DROP the previous SCHEMA COLLECTION 
IF EXISTS(  
SELECT * FROM sys.xml_schema_collections   
WHERE name = 'x_validator' )
BEGIN 
	DROP XML SCHEMA COLLECTION x_validator 
END 
GO 
CREATE XML SCHEMA COLLECTION x_validator
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="x">
        <xsd:simpleType>
			<xsd:restriction base="xsd:token">
				<xsd:pattern value="[0-9]{3}-[0-9]{2}-[0-9]{4}"/>
			</xsd:restriction>
		</xsd:simpleType>
    </xsd:element>
</xsd:schema>'
GO
-- Verify - list of collections in the database.
SELECT *
FROM sys.xml_schema_collections;
GO
DECLARE @x XML(x_validator)
DECLARE @CheckTest NVARCHAR(50) = '555-09-2845'
BEGIN TRY
	SELECT @x = '<x>' + @CheckTest + '</x>'
	SELECT 'Validateion Succeeded for ' + @CheckTest AS Result
END TRY
BEGIN CATCH
	SELECT 'Validation Failed for ' + @CheckTest AS Result
END CATCH
GO

DECLARE @x XML(x_validator)
DECLARE @CheckTest NVARCHAR(50) = '555-09-245'
BEGIN TRY
	SELECT @x = '<x>' + @CheckTest + '</x>'
	SELECT 'Validateion Succeeded for ' + @CheckTest AS Result
END TRY
BEGIN CATCH
	SELECT 'Validation Failed for ' + @CheckTest AS Result
END CATCH
GO
CREATE FUNCTION dbo.x_validator(
@checked_variable NVARCHAR(MAX)
)
RETURNS BIT
BEGIN
	DECLARE @x XML(x_validator)
	SELECT @x = '<x>' + @checked_variable + '</x>'
	RETURN 1 -- 'True'
END
GO
CREATE TABLE dbo.Regex_column(
		id int not null identity(1,1)
		, matched_text nvarchar(11) CONSTRAINT Regex_x_validator CHECK (dbo.x_validator(matched_text) = 1)
		)
GO
DECLARE @CheckTest NVARCHAR(50) = '555-09-2845'
BEGIN TRY
	INSERT INTO dbo.Regex_column(matched_text)
	VALUES
	(@CheckTest)
	SELECT 'Insert Succeeded for ' + @CheckTest AS Result
END TRY
BEGIN CATCH
	SELECT 'Insert Failed for ' + @CheckTest AS Result
END CATCH
GO
DECLARE @CheckTest NVARCHAR(50) = '555-09-2845'
BEGIN TRY
	INSERT INTO dbo.Regex_column(matched_text)
	VALUES
	('555-09-245')
	SELECT 'Insert Succeeded for ' + @CheckTest AS Result
END TRY
BEGIN CATCH
	SELECT 'Insert Failed for ' + @CheckTest AS Result
END CATCH
GO
DECLARE @CheckTest NVARCHAR(50) = '555-09-2846'
DECLARE @CheckTest2 NVARCHAR(50) = '555-09-2847'
BEGIN TRY
	INSERT INTO dbo.Regex_column(matched_text)
	VALUES
	(@CheckTest)
	,(@CheckTest2)
	SELECT 'Insert Succeeded for ' + @CheckTest + ' and ' + @CheckTest2 AS Result
END TRY
BEGIN CATCH
	SELECT 'Insert Failed for ' + @CheckTest + ' and ' + @CheckTest2 AS Result
END CATCH
GO
-- Should fail as one does not pass
DECLARE @CheckTest NVARCHAR(50) = '555-09-2848'
DECLARE @CheckTest2 NVARCHAR(50) = '555-09-249'-- missing number in last part
BEGIN TRY
	INSERT INTO dbo.Regex_column(matched_text)
	VALUES
	(@CheckTest)
	,(@CheckTest2)
	SELECT 'Insert Succeeded for ' + @CheckTest + ' and ' + @CheckTest2 AS Result
END TRY
BEGIN CATCH
	SELECT 'Insert Failed for ' + @CheckTest + ' and ' + @CheckTest2 AS Result
END CATCH
GO
SELECT *
FROM dbo.Regex_column
